/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package org.dusin.teachassistdbservice.dao4mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.dusin.teachassistdbservice.dao.HomeworkDao;

import org.dusin.teachassistdbservice.util.DateUtil;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author dusin
 */
public class HomeworkDaoImpl implements HomeworkDao{
    static Logger logger = Logger.getLogger(HomeworkDaoImpl.class);
    private JdbcTemplate jdbcTemplate;
    
    public HomeworkDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    
    @Override
    public boolean doCreate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "insert into homework(teachclassid,no,title,duedate,type,note) values(?,?,?,?,?,?)";
        logger.debug(sql+"?"+bean);
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    //返回自增长ID
    @Override
    public long doCreateWithGetId(final Object[] bean) {
        long flag = -1;
        final String sql = "insert into homework(teachclassid,no,title,duedate,type,note) values(?,?,?,?,?,?)";
        logger.debug(sql+"?"+bean);
        KeyHolder keyHolder = new GeneratedKeyHolder();
     
        int rows = jdbcTemplate.update(new PreparedStatementCreator(){

            @Override
            public PreparedStatement createPreparedStatement(Connection con)
                    throws SQLException {
                 PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);                 
                 ps.setInt(1, (Integer)bean[0]);
                 ps.setInt(2, (Integer)bean[1]);
                 ps.setString(3, (String)bean[2]);
                 ps.setString(4, (String)bean[3]);      
                 ps.setInt(5, (Integer)bean[4]);
                 ps.setString(6,(String)bean[5]);
                 return ps;
            }
        }, keyHolder);
        
        if (rows > 0) {
            flag = keyHolder.getKey().longValue();
        }
        return flag;
    }
    
    @Override
    public boolean isScoreExisted(int jobId,String studentNo){
        String sql = null;
        sql = "select count(*) from homeworkdetail where jobId= ? and studentNo=?";
        int c =  jdbcTemplate.queryForObject(sql,Integer.class,jobId,studentNo);
        return c>0?true:false;
    }
    
        
    @Override
    public boolean doChangeStatus(int id,int status) {
        boolean flag = true;
        String sql = null;
        sql = "update homework set status=? where id = ?";
        int rows = jdbcTemplate.update(sql, status,id);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doSubmitReport(Object[] bean){
        boolean flag = true;
        String sql = null;
        sql = "insert into homeworkdetail(jobId,studentno,score,time,count,status,path,note) values(?,?,?,?,?,?,?,?)";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    
    //批量创建所有学生记录
    @Override
    public boolean doCreateDetailBatch(List<Object[]> beans){
        boolean flag = true;
        String sql = null;
        sql = "insert into homeworkdetail(jobId,studentno,score,time,count,status,path,note) values(?,?,?,?,?,?,?,?)";
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        return true;
    }
    
    //评分后记录不可修改
    @Override
    public int[]  doUpdateScoreBatch(List<Object[]> beans){
        boolean flag = true;
        String sql = null;
        sql = "update homeworkdetail set score=?,status=3,note=? where jobId=?   and studentNo=?";
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        return rows;
    }
    
    // 重新提交报告  status--0 未提交；status-1 已提交; status-3 已评分，不可修改,score==0表示已提交
    @Override 
    public boolean doUpdateReport(int jobId,String studentNo,String filename){
        boolean flag = true;
        String time = DateUtil.get(new Date());
        String sql = null;
        sql = "update homeworkdetail set score=0,status=1 ,time=?,count=count+1 ,path=? where jobId=? and studentno=? and status<>3";
        int rows = jdbcTemplate.update(sql,time,filename, jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    // 取消报告提交，删除文件并更改状态  status=0,表示报告未提交, score=0表示报告未提交
    @Override 
    public boolean doCancelReport(int jobId,String studentNo){
        boolean flag = true;
        String sql = null;
        sql = "update homeworkdetail set score=-1,status=0 ,time=null where jobId=? and studentno=? and status<>3";
        int rows = jdbcTemplate.update(sql, jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    //status==3 已评分，学生不可再提交修改
    @Override
    public boolean doSetScore(int jobId,String studentNo,int score,String note){
        boolean flag = true;
        String sql = null;
        sql = "update homeworkdetail set status=3 ,score=?,note=? where jobId=? and studentno=? ";
        int rows = jdbcTemplate.update(sql, score,note,jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    //修改状态 status==3 
    @Override
    public boolean doSetStatus(int jobId,String studentNo,int status){
        boolean flag = true;
        String sql = null;
        sql = "update homeworkdetail set status=?  where jobId=? and studentno=? ";
        int rows = jdbcTemplate.update(sql, status,jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doDeleteByID(int id) {
        boolean flag = true;
        String sql = null;
        sql = "delete from homework where id = ?";
        logger.debug(sql);
        int rows = jdbcTemplate.update(sql, id);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    @Override
    public boolean doUpdate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "update homework set no=?,title=?,duedate=?,note=? where id = ?";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    @Override
    public Map<String, Object> findByNo(String no) {
        String sql = null;
        sql = "select * from teachclass where teachclassno=?";
        return jdbcTemplate.queryForMap(sql,no);
    }

    @Override
    public List<Map<String, Object>> findAll() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAll(int start, int size) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAllByTeachClassId(int teachClassId,int type) {
        String sql = null;
        sql = "select * from vhomework where teachclassid=? and type=? order by no";
        return jdbcTemplate.queryForList(sql,teachClassId,type);
    }
    
    @Override
    public List<Map<String,Object>> findAllCourseJobs4Student(int teachClassId,String studentNo){
        String sql = null;
        sql = "select t1.id as id, no,title,duedate,score,jstatus,jnote,time,count,sstatus ,path,snote  from  "
                +"(select homework.id as id, no,title,duedate,homework.status as jstatus,homework.note as jnote from homework where teachclassid=? )  t1 left join "
                +" (select jobid,score,time,count,homeworkdetail.status as sstatus,path,homeworkdetail.note as snote from homeworkdetail where  studentNo =?) t2 "
                + " on id = jobid order by no";
        return jdbcTemplate.queryForList(sql,teachClassId,studentNo);
    }

    @Override
    public List<Map<String, Object>> findHomeworkDetail(int teachclassId, int homeworkId) {
        String sql = null;
        sql = "select no,t1.studentno,name,ifnull(score,0) score,ifnull(status,0) status,time,count,path,anote note from (select no,studentclass.studentno,name from studentclass left join student on studentclass.studentno=student.studentno where classID = ? order by no) t1 left join (select studentno,score,status,time,count,path,note as anote from homeworkdetail where jobid=?) t2 on t1.studentno = t2.studentno";
        return jdbcTemplate.queryForList(sql,teachclassId,homeworkId);
    }

    @Override
    public List<Map<String, Object>> findAllByTeachClassId(int teachClassId) {
        String sql = null;
        sql = "select * from homework where teachclassid=?  order by no";
        return jdbcTemplate.queryForList(sql,teachClassId);
    }

    @Override
    public List<Map<String, Object>> findAllHomeworkDetail(int teachClassId) {
        String sql = "select * from homeworkdetail where jobId in (select id from homework where teachclassid = ?)";
        return jdbcTemplate.queryForList(sql,teachClassId);
    }   
}
